package com.gjun.struts.resource;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.gjun.struts.bean.GjunMonthShopBean;
import com.gjun.struts.bean.Gjunmonthdetails;

public class MonthReportDAO extends HibernateDaoSupport implements
		MonthReportDAOIf {

	public Map<String,List<GjunMonthShopBean>> loadMonthDetails(String province,
			String city, String storeCode, String month, String orderBy,
			final String limit, String month2) {
		
		Map<String,List<GjunMonthShopBean>> reportList = new HashMap<String,List<GjunMonthShopBean>>();
		String hql = "select shop.id from Gjunshops shop ";
		// 先求出需要查询的分店
		if(!province.equals("--请选择--")){
			hql += "where shop.province='"+province+"' ";
		}
		if(city.trim().length()>0){
			hql += "and shop.city='"+city+"' ";
		}
		if(!storeCode.equals("不区分当前城市店铺") && storeCode.trim().length()>0){
			hql += "and shop.storecode='"+storeCode+"' ";
		}
		
		
		final String query = hql;
		List<Integer> shopIds = (List<Integer>)this.getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				Query q = session.createQuery(query);
				
				return q.list();
			}
		});
		
		
		String ids = "";
		for(Integer i : shopIds){
			ids+=i+",";
		}
		ids = ids.substring(0,ids.length()-1);
		System.out.println(ids);
		
		List<GjunMonthShopBean> report1 = loadReportData(month, orderBy, limit, ids);
		System.out.println(month+"   report1:  "+report1);
		reportList.put(month,report1);
		
		if(month2 !=null && month2.trim().length()>0){
			List<GjunMonthShopBean> report2 = loadReportData(month2, orderBy, limit, ids);
			reportList.put(month2,report2);
		}
		//System.out.println(hql);
		//System.out.println(hql2);
		return reportList;
	}

	private List<GjunMonthShopBean> loadReportData(String month, String orderBy, final String limit, String ids) {
		String hql2 = "select new com.gjun.struts.bean.GjunMonthShopBean(gmonth,shop.storecode) from Gjunmonthdetails gmonth,Gjunshops shop where gmonth.year||'-'||gmonth.month='"+month+"' and gmonth.shopid in ("+ids+") and gmonth.shopid=shop.id ";
		
		if(orderBy==null || orderBy.equals("根据销售额升序")){
			hql2 += " order by gmonth.totalincome";
		}else{
			hql2 += " order by gmonth.totalincome desc";
		}
		final String query2 = hql2;
		List<GjunMonthShopBean> reports = (List<GjunMonthShopBean>)this.getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				Query q = session.createQuery(query2);
				if(limit!=null && !limit.equals("所有数据")){
					
					Integer it = Integer.parseInt(limit);
					q.setFirstResult(0);
					q.setMaxResults(it*5);
				}
				return q.list();
			}
		});
		return reports;
	}
	
	public static void main(String[] args) {
		ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
		MonthReportDAOIf dao = (MonthReportDAOIf)context.getBean("monthReportDAO");
		Map<String,List<GjunMonthShopBean>> map = dao.loadMonthDetails("广东","广州","","2010-8",null,null,null);
		System.out.println(map.size());
		List<GjunMonthShopBean> list1 = map.get("2010-8");
		System.out.println(list1.size());
		
		
	}

}
